const router = require('koa-router')()
const db = require('../db/initdb');
const fs = require('fs')
const path = require('path')
const { koaBody } = require('koa-body');

router.prefix('/works')

router.get('/', async (ctx, next) => {
  ctx.body = 'this is a works response!'
})

router.get('/data', async (ctx, next) => {
  let sql = "select * from hs.works";
  let result = await db.query(sql);
  let res = [];

  for (let element of result.rows) {
    let sql = "select hs.user.name from hs.user where hs.user.id=$1";
    let result = await db.query(sql, [element.userid]);

    element.name = result.rows[0].name
    if (element.picture) {
      element.type = '视频'
    }
    else {
      element.type = '图文'
    }

    let sql2 = 'select * from hs.picaddress where workid=$1';
    let result2 = await db.query(sql2, [element.id]);

    if (result2.rows[0] && result2.rows[0].address) {
      element.address = result2.rows[0].address
    }
    else {
      element.address = ''
    }
    
    if (result2.rows[0] && result2.rows[0].perface) {
      element.perface = result2.rows[0].perface
    }
    else {
      element.perface = ''
    }


    res.push(element)
  }


  ctx.body = res

})

router.get('/details', async (ctx, next) => {
  let sql = "select * from hs.works where hs.works.id=$1";
  let result = await db.query(sql, [ctx.query.id]);
  let res = [];

  for (let element of result.rows) {
    let sql2 = 'select * from hs.picaddress where workid=$1';
    let result2 = await db.query(sql2, [element.id]);


    if (result2.rows[0] && result2.rows[0].address) {
      element.address = result2.rows[0].address
    }
    else {
      element.address = ''
    }
    
    if (result2.rows[0] && result2.rows[0].perface) {
      element.perface = result2.rows[0].perface
    }
    else {
      element.perface = ''
    }

    if (element.picture) {
      element.type = 'video'
    }
    else {
      element.type = 'image-text'
    }
    res.push(element)
  }

  ctx.body = res
})

router.get('/check', async (ctx, next) => {
  let audit = ctx.query.audit;
  if (audit == 'false') {
    audit = 'true'
  }
  else {
    audit = 'false'
  }
  let sql = 'UPDATE hs.works SET audit=$1 where id=$2'
  let result = await db.query(sql, [audit, ctx.query.id])

  console.log(result)

  if (result.rowCount > 0) {
    ctx.body = 'success'
  }
  else {
    ctx.body = 'fail'
  }
})

// router.post('/search', async (ctx, next) => {
//   const body = ctx.request.body;
//   console.log(body);
//   if (body.author != undefined) {
//     let sql = 'select hs.user.id from hs.user where name=$1'
//     let result = await db.query(sql, [body.author]) 
//     let userid = result.rows[0].id

//     if (body.type == 'all') {
//       let sql = 'select * from hs.works where id=$1 and title=$2 and userid=$3'
//       let result = await db.query(sql, [body.ID, body.title, userid])
  
//       console.log(result)
  
//       if (result.rowCount > 0) {
//         ctx.body = result
//       }
//       else {
//         ctx.body = 'fail'
//       }
//     }
//   }




//   ctx.body = 'success'
// })

router.get('/image', async (ctx, next) => {
  let filePath = path.join(__dirname, '../', ctx.query.perface)
  let fileContent = fs.readFileSync(filePath)

  ctx.body = fileContent
})

router.get('/video', async (ctx, next) => {
  let filePath = path.join(__dirname, '../', ctx.query.address)
  let fileContent = fs.readFileSync(filePath)

  ctx.body = fileContent
})

router.post('/upload/perface', koaBody(), async (ctx, next) => {
  let id = ctx.query.id;
  let filename = (ctx.request.files.file.newFilename);
  console.log(id)
  console.log(filename)

  ctx.body = {
    code: 200,
    perface: filename
  }
})

router.post('/upload/address', koaBody(), async (ctx, next) => {
  let id = ctx.query.id;
  let filename = (ctx.request.files.file.newFilename);
  console.log(id)
  console.log(filename)

  ctx.body = {
    code: 200,
    address: filename
  }
})

router.post('/update', async (ctx, next) => {
  let picture
  let id = ctx.query.id
  let body = ctx.request.body
  console.log('id: ', id)
  console.log('body: ', body)
  if (body.type == '视频') {
    picture = true
  }
  else {
    picture = false
  }

  let sql = 'UPDATE hs.works SET title=$1, date=$2, picture=$3, detail=$4 where id=$5'
  await db.query(sql, [body.title, body.createTime, picture, body.description, id])

  let sql2 = 'UPDATE hs.picaddress SET perface=$1, address=$2 where workid=$3'
  await db.query(sql2, [body.page, body.content, id])

  ctx.body = 'success'

})

router.get('/comments', async (ctx, next) => {
  let id = ctx.query.id
  let sql = 'select * from hs.comments where workid=$1'
  let result = await db.query(sql, [id])
  let res = []

  for (let element of result.rows) {
    let sql = "select hs.user.name from hs.user where hs.user.id=$1";
    let result = await db.query(sql, [element.userid]);

    element.name = result.rows[0].name

    res.push(element)
  }

  ctx.body = res
})

router.get('/data1', async (ctx, next) => {
  let sql = 'select * from hs.card'
  let result = await db.query(sql)

  ctx.body = result.rows
})

router.get('/data2', async (ctx, next) => {
  let sql = 'select * from hs.user'
  let result = await db.query(sql)

  ctx.body = result.rows 
})

router.get('/data3', async (ctx, next) => {
  let sql = 'select * from hs.works'
  let result = await db.query(sql)

  ctx.body = result.rows 
})

router.get('/data4', async (ctx, next) => {
  let sql = 'select * from hs.works where picture=true'
  let result = await db.query(sql)

  ctx.body = result.rows
})

router.get('/data5', async (ctx, next) => {
  let sql = 'select * from hs.works where picture=false'
  let result = await db.query(sql)

  ctx.body = result.rows 
})

router.get('/data6', async (ctx, next) => {
  let sql = 'select * from hs.comments'
  let result = await db.query(sql)

  ctx.body = result.rows
})

router.get('/hotdata', async (ctx, next) => {
  let sql = 'select * from hs.card'
  let result = await db.query(sql)
  let arr = result.rows

  arr.sort((a, b) => b.voicenum - a.voicenum)

  ctx.body = arr
})

router.get('/worksdata', async (ctx, next) => {
  let sql = 'select * from hs.works'
  let result = await db.query(sql)
  let arr = result.rows

  arr.sort((a, b) => b.likenum - a.likenum)

  ctx.body = arr
})


module.exports = router
